﻿using NetCore.ORM;
using NetCore.BLL;
using NetCore.Code.Model;
using NetCore.Model;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using T5.TextTemplating;
using NetCore.ORM.Ado;
using Microsoft.AspNetCore.Http;
using System.Threading;

namespace NetCore.Code.BLL
{
    public class DbCodeService : SerivceBase, IDbCodeService
    {

        /// <summary>
        /// 系统定义字段
        /// </summary>
        public string[] SystemColumnNames {
            get {
                return new string[] { "createby", "createdate", "lastmodifyuserid", "lastmodifydate", "isdeleted" };
            }
        }

        /// <summary>
        /// 验证数据库是否存在
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool IsDbServerExist(DbServerEntity entity)
        {
            var query = Expression.And(Expression.Eq("IsDeleted", false)
                , Expression.And(Expression.Eq("DbServerName", entity.DbServerName),
                Expression.Not(Expression.Eq("DbServerId", entity.DbServerId))));
            var t = this.Query<DbServerEntity>(query).FirstOrDefault();
            if (t != null)
            {
                return true;
            }
            return false;
        }
        /// <summary>
        /// 验证表是否存在
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool IsDbTableExist(DbTableEntity entity)
        {
            var query = Expression.And(
                Expression.And(Expression.Eq("DbServerId", entity.DbServerId),
                Expression.Eq("IsDeleted", false))
                , Expression.And(Expression.Eq("DbTableName", entity.DbTableName),
                Expression.Not(Expression.Eq("DbTableId", entity.DbTableId))));
            var t = this.Query<DbTableEntity>(query).FirstOrDefault();
            if (t != null)
            {
                return true;
            }
            return false;
        }


        /// <summary>
        /// 保存表信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public virtual bool DbTableSave(DbTableEntity entity, ref string strError)
        {
            if (IsDbTableExist(entity))
            {
                strError = "表已存在，请不要重复添加！";
                return false;
            }
            entity.LastModifyDate = DateTime.Now;
            entity.LastModifyUserId = ApplicationEnvironments.DefaultSession.UserId;
            entity.CreateBy = ApplicationEnvironments.DefaultSession.UserId;
            entity.CreateDate = DateTime.Now;
            if (entity != null)
            {
                var server = this.GetById<DbServerEntity>(entity.DbServerId);
                if (server != null)
                {
                    entity.ProviderType = server.ProviderType;
                }
            }
            return this.SaveOrUpdate<DbTableEntity>(entity);

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="KeyId"></param>
        /// <param name="dbServerType"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool DbDelete(string KeyId, string dbServerType, ref string strError)
        {

            if (!string.IsNullOrEmpty(KeyId))
            {
                if (dbServerType.Equals("0"))
                {
                    this.Delete<DbServerEntity>(KeyId);
                }
                else if (dbServerType.Equals("2"))
                {
                    this.Delete<DbTableEntity>(KeyId);

                }
                // mSessionFactory.Flush();
            }
            return true;
        }

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual DbTableEntity GetTableById(int id)
        {

            return this.GetById<DbTableEntity>(id);
        }
        /// <summary>
        /// 通过模板生成文件
        /// </summary>
        /// <param name="tableId"></param>
        /// <param name="tempid"></param>
        /// <param name="strResult"></param>
        /// <returns></returns>
        public virtual bool CreateCode(int tableId, string templateid, ref string strResult)
        {
            Dictionary<string, string> dicCode = new Dictionary<string, string>();
            return this.CreateCode(tableId, templateid, ref strResult, ref dicCode);

        }

        /// <summary>
        /// 通过模板生成文件
        /// </summary>
        /// <param name="tableId">表id</param>
        /// <param name="templateid">模板id</param>
        /// <param name="strResult">返回字符串</param>
        /// <param name="dicCode">返回文件集合</param>
        /// <returns></returns>
        public virtual bool CreateCode(int tableId, string templateid, ref string strResult, ref Dictionary<string, string> dicCode)
        {
            if (tableId <= 0)
            {
                strResult = "参数错误";
                return false;
            }
            TemplateGenerator generator = new TemplateGenerator();
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(Uri).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(System.Linq.Enumerable).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(System.Reflection.Assembly).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(System.Runtime.GCSettings).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(JsonConvert).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(JSONHelper).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(DbColumnEntity).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(UserEntity).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(UserService).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(CodeDomain).Assembly.Location));
            return CreateCode(generator, tableId.ToString(), templateid, ref strResult, ref dicCode);
        }

        /// <summary>
        /// 批量生成文件
        /// </summary>
        /// <param name="tableId"></param>
        /// <param name="templateid"></param>
        /// <param name="strError">错误信息</param>
        /// <param name="dicCode"></param>
        /// <returns></returns>
        public async Task<bool> CreateCodes(string tableId, string templateid, int round = 0, int maxRound = 0)
        {
            if (string.IsNullOrWhiteSpace(tableId))
            {
                return false;
            }
            string rootPath = ApplicationEnvironments.BaseDirectory + ApplicationEnvironments.TempPath + "code/";

            TemplateGenerator generator = new TemplateGenerator();
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(Uri).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(System.Linq.Enumerable).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(System.Reflection.Assembly).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(System.Runtime.GCSettings).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(JsonConvert).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(JSONHelper).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(DbColumnEntity).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(UserEntity).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(UserService).Assembly.Location));
            generator.ReferencePaths.Add(Path.GetDirectoryName(typeof(CodeDomain).Assembly.Location));
            string[] tables = tableId.Split(new char[] { ',', ';' });
            if (tables != null && tables.Length > 0)
            {
                await Task.Factory.StartNew<bool>(() =>
                {
                    foreach (var tableid in tables)
                    {
                        if (!string.IsNullOrWhiteSpace(tableid))
                        {
                            string strResult = "";


                            var dicCode = new Dictionary<string, string>();
                            if (CreateCode(generator, tableid, templateid, ref strResult, ref dicCode))
                            {
                                if (dicCode != null && dicCode.Count > 0)
                                {
                                    foreach (var code in dicCode)
                                    {
                                        FileHelper.WriteLine(rootPath + code.Key, code.Value);
                                    }
                                }
                            }


                        }
                    }
                    if (maxRound == round)
                    {
                        if (Directory.Exists(rootPath))
                        {
                            if (File.Exists(ApplicationEnvironments.RootPath + ApplicationEnvironments.TempPath + "code.zip"))
                            {
                                File.Delete(ApplicationEnvironments.RootPath + ApplicationEnvironments.TempPath + "code.zip");
                            }
                            if (!Directory.Exists(ApplicationEnvironments.RootPath + ApplicationEnvironments.TempPath))
                            {
                                Directory.CreateDirectory((ApplicationEnvironments.RootPath + ApplicationEnvironments.TempPath).ToLower());
                            }
                            System.IO.Compression.ZipFile.CreateFromDirectory(rootPath,
                                (ApplicationEnvironments.RootPath + ApplicationEnvironments.TempPath + "code.zip").ToLower(), System.IO.Compression.CompressionLevel.Optimal, true);
                            Directory.Delete(rootPath, true);
                        }

                    }
                    return true;
                });
            }
            return true;
        }

        public bool CreateCode(TemplateGenerator generator, string tableId, string templateid, ref string strResult, ref Dictionary<string, string> dicCode)
        {
            Regex regexNamespace = new Regex("namespace\\s([a-zA-Z.]+.BLL|[a-zA-Z.]+.Model|[a-zA-Z.]+.Web.Controllers)");
            Dictionary<string, string> lstCreateCode = new Dictionary<string, string>();
            var tempEntity = this.GetById<DbTemplateEntity>(templateid);
            var tableEntity = this.GetById<DbTableEntity>(tableId.ToInt());
            if (tempEntity == null || tableEntity == null)
            {
                strResult = "模板不存在";
                return false;
            }
            this.GetTemplate(tempEntity, lstCreateCode);
            CodeDomain domain = new CodeDomain(tableEntity);
            Regex regexClass = new Regex("\\s(I?" + domain.ClassName + "(Entity|Service|Map|Controller))");

            if (lstCreateCode != null && lstCreateCode.Count > 0)
            {
                string rootPath = tempEntity.Path.Substring(0, tempEntity.Path.IndexOf("/")) + "/";
                StringBuilder stringBuilder = new StringBuilder();
                foreach (var item in lstCreateCode)
                {
                    string outfile = "";
                    string outcontent = "";
                    generator.ProcessTemplate(null, item.Value.Replace("{0}", tableId.ToString()), ref outfile, out outcontent);
                    stringBuilder.Append(outcontent);
                    if (generator.Errors != null && generator.Errors.Count > 0)
                    {
                        strResult = generator.Errors.ToJson();
                        return true;
                    }
                    string nNamespace = "";
                    var matchNamespace = regexNamespace.Match(outcontent);
                    string path = "";
                    if (matchNamespace.Success)
                    {
                        nNamespace = matchNamespace.Groups[1].Value;
                    }
                    string className = "";
                    var regexClassMatch = regexClass.Match(outcontent);
                    if (regexClassMatch.Success)
                    {
                        className = regexClassMatch.Groups[1].Value;
                        path = rootPath + nNamespace + "/" + string.Format((item.Key.IndexOf("I") == 0 ? item.Key.Substring(1) : item.Key), domain.ClassName) + "/" + className + outfile;
                    }
                    if (!matchNamespace.Success)
                    {
                        nNamespace = "Views";
                        className = domain.ClassName;
                        path = rootPath + nNamespace + "/" + domain.ClassName + "/" + item.Key + outfile;
                    }
                    else {
                        if (string.IsNullOrWhiteSpace(path))
                        {
                            path = rootPath + nNamespace + "/" + domain.ClassName + "/" + item.Key + outfile;
                        }
                    }
                    dicCode.Add(path, outcontent);
                }
                strResult = stringBuilder.ToString();
            }
            return true;
        }

        /// <summary>
        /// 获取可生成的模板列表
        /// </summary>
        /// <param name="tempEntity">上级模板id</param>
        /// <param name="lstCreateCode">需要生成的模板集合</param>
        public void GetTemplate(DbTemplateEntity tempEntity, Dictionary<string, string> lstCreateCode)
        {
            string templateLine = "";
            var lstTemp = this.Query<DbTemplateEntity>(Expression.And(Expression.Eq(DbTemplateEntity.__IsDeleted, 0), Expression.Eq(DbTemplateEntity.__ParentId, tempEntity.TemplateId)));
            if (lstTemp != null && lstTemp.Count > 0)
            {
                foreach (var item in lstTemp)
                {

                    var children = this.Query<DbTemplateEntity>(Expression.And(Expression.Eq(DbTemplateEntity.__IsDeleted, 0), Expression.Eq(DbTemplateEntity.__ParentId, item.TemplateId)));
                    if (children != null && children.Count > 0)
                    {
                        foreach (var citem in children)
                        {
                            templateLine = FileHelper.ReadLine((DbTemplateService.RootPath + citem.Path + citem.TemplateName + ".tt").ToLower());
                            if (!string.IsNullOrWhiteSpace(templateLine))
                            {
                                lstCreateCode.Add(citem.TemplateName, templateLine);
                            }
                        }
                    }
                    templateLine = FileHelper.ReadLine((DbTemplateService.RootPath + item.Path + item.TemplateName + ".tt").ToLower());
                    if (!string.IsNullOrWhiteSpace(templateLine))
                    {
                        lstCreateCode.Add(item.TemplateName, templateLine);
                    }
                }
            }
            templateLine = FileHelper.ReadLine((DbTemplateService.RootPath + tempEntity.Path + tempEntity.TemplateName + ".tt").ToLower());
            if (!string.IsNullOrWhiteSpace(templateLine))
            {
                lstCreateCode.Add(tempEntity.TemplateName, templateLine);
            }
        }

        /// <summary>
        /// 获取表名
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="IsTable"></param>
        /// <returns></returns>
        public virtual List<DbTableEntity> GetDbTable(int id, bool IsTable)
        {

            DbServerEntity entity = this.GetById<DbServerEntity>(id);
            List<Hashtable> lstHsh = new List<Hashtable>();
            List<DbTableEntity> lstEntity = new List<DbTableEntity>();

            var dbHelper = new AdoRepository((ProviderType)entity.ProviderType, entity.ConntionString);
            //switch ((ProviderType)entity.ProviderType)
            //{
            //    case ProviderType.MsSql:
            //        db = new DbSQLServer((ProviderType)entity.ProviderType, entity.ConntionString);
            //        break;
            //    case ProviderType.MySQL:
            //        db = new DbMySQL((ProviderType)entity.ProviderType, entity.ConntionString);
            //        break;
            //}
            List<Dictionary<string, object>> lstTable = IsTable ? dbHelper.GetTable(entity.DbServerName) : dbHelper.GetView(entity.DbServerName);
            ICriterion criter = Expression.And(
                              Expression.Eq(DbTableEntity.__DbServerId, entity.DbServerId),
                           Expression.Eq(DbTableEntity.__IsDeleted, false));
            lstEntity = this.Query<DbTableEntity>(criter, Order.Asc(DbTableEntity._PrimaryKeyName)).ToList();
            if (lstTable != null && lstTable.Count > 0)
            {
                lstEntity.ForEach(x => {
                    x.DbTableName = (x.DbTableName != x.LocalName) ? x.DbTableName + "(" + x.LocalName + ")" : x.DbTableName;
                });
                using (var tran = this.Begin())
                {
                    try
                    {
                        foreach (Dictionary<string, object> table in lstTable)
                        {
                            var tablearr = table.ToArray();
                            var exist = lstEntity.Where(x => x.DbTableName.ToLower().Equals(tablearr[1].Value.ToStr().ToLower())).FirstOrDefault();
                            if (exist != null)
                            {
                                exist.ProviderType = entity.ProviderType;
                                //  lstEntity.Add(exist);
                                continue;
                            }
                            var table1 = new DbTableEntity()
                            {
                                DbServerId = entity.DbServerId,
                                LocalName = tablearr[1].Value.ToString(),
                                DbTableName = tablearr[1].Value.ToString(),
                                IsSystem = false,
                                IsTable = tablearr[4].Value.ToBool(),
                                IsUpdated = false,

                                IsEnableCreate = true,
                                IsEnableIsDeleted = true,
                                IsEnableLastModify = true,
                                ProviderType = entity.ProviderType
                            };
                            lstEntity.Add(table1);
                            this.SaveOrUpdate<DbTableEntity>(table1);
                        }
                        this.Commit();
                    }
                    catch (Exception)
                    {
                        this.Rollback();
                    }
                }

            }
            if (lstEntity != null && lstEntity.Count > 0)
            {
                lstEntity = lstEntity.Where(x => x.IsTable == IsTable).ToList();
            }
            return lstEntity;
        }

        /// <summary>
        /// 获取所有字段
        /// </summary>
        /// <param name="tableid"></param>
        /// <returns></returns>
        public virtual List<DbColumnEntity> GetColumns(int tableid)
        {
            int providerType = ProviderType.SqlServer.GetHashCode();
            DbTableEntity entity = new DbTableEntity();
            return GetColumns(tableid, ref entity, ref providerType);
        }
        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="entity"></param>
        /// <param name="providerType">数据库类型</param>
        /// <returns></returns>
        public virtual List<DbColumnEntity> GetColumns(int tableid, ref DbTableEntity entity, ref int providerType)
        {
            return GetColumns(tableid, ref entity, ref providerType, true);
        }
        /// <summary>
        /// 获取字段
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="entity"></param>
        /// <param name="providerType">数据库类型</param>
        /// <param name="isSyncColumn">同步字段</param>
        /// <returns></returns>
        public virtual List<DbColumnEntity> GetColumns(int tableid, ref DbTableEntity entity, ref int providerType, bool isSyncColumn = true)
        {
            if ((entity == null || entity.DbTableId <= 0) && tableid > 0)
            {
                entity = this.GetById<DbTableEntity>(tableid);
            }
            if (entity == null)
            {
                return null;
            }

            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId);
            if (dbServer == null)
            {
                return null;
            }

            ICriterion criter = Expression.And(
                               Expression.Eq("DbTableId", tableid),
                              Expression.Eq("IsDeleted", false));

            List<DbColumnEntity> lstEntity = new List<DbColumnEntity>();
            lstEntity = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            entity.ProviderType = dbServer.ProviderType;
            if (!isSyncColumn)
            {
                return lstEntity;
            }

            var dbHelper = this.CreateRepository((ProviderType)dbServer.ProviderType, dbServer.ConntionString);
            List<Dictionary<string, object>> dbColumns = dbHelper.GetDbColumn(entity.DbTableName, dbServer.DbServerName, entity.IsTable);
            var lstcodeType = this.Query<SysFieldTypeEntity>(Expression.Eq(SysFieldTypeEntity.__IsDeleted, false));
            var lstDbType = AppHttpContext.GetSerivce<IDbFieldRelationTypeService>().GetAll((ProviderType)dbServer.ProviderType);
            if (dbColumns != null && dbColumns.Count > 0)
            {
                int index = 0;
                using (var tran = this.Begin())
                {
                    try
                    {

                        foreach (Dictionary<string, object> item in dbColumns)
                        {
                            index++;
                            var column = item.ToArray();
                            var exist = lstEntity.Where(x => x.ColumnName.ToLower().Equals(column[1].Value.ToString().ToLower())).FirstOrDefault();
                            if (exist != null)
                            {
                                //  lstEntity.Add(exist);
                                continue;
                            }

                            int DbTypeId = 1;
                            int sysDbTypeid = 2;
                            var dbType = lstDbType.Where(x => x.DbTypeName == column[11].Value.ToStr()).FirstOrDefault();
                            if (dbType != null)
                            {
                                DbTypeId = dbType.DbFieldRelationTypeId;
                                sysDbTypeid = dbType.SysFieldTypeId;
                            }
                            SysFieldTypeEntity sysDbType = lstcodeType.Where(x => x.SysFieldTypeId == sysDbTypeid).FirstOrDefault();
                            if (sysDbType == null)
                            {
                                sysDbType = new SysFieldTypeEntity
                                {
                                    ControlType = 1,
                                    VaildKey = "",
                                    SysFieldTypeId = 2
                                };
                            }
                            var dbCol = new DbColumnEntity()
                            {
                                ColumnId = MyGenerateHelper.GenerateOrder(),
                                DbTableId = entity.DbTableId,
                                OrderNo = column[0].Value.ToInt(),
                                Format = "",
                                ColumnName = column[1].Value.ToStr().Trim(),
                                ColumnCaption = column[10].Value.ToStr().Trim(),
                                ColumnKeyName = "lbl_" + entity.DbTableName + "_" + column[1].Value.ToString(),
                                IsIdentifier = column[2].Value.ToBool(),
                                IsPrimary = column[3].Value.ToBool(),
                                MaxLength = column[6].Value.ToInt(),
                                AllowDBNull = column[8].Value.ToBool(),
                                DefaultValue = column[9].Value.ToStr().Replace("(", "").Replace(")", ""),
                                Remark = column[10].Value.ToStr(),
                                DbFieldRelationTypeId = DbTypeId,
                                DbTypeName = column[11].Value.ToStr(),
                                IsGridVisible = true,
                                ControlType = sysDbType.ControlType,
                                IsEnabled = true,
                                IsEditVisible = !column[3].Value.ToBool(),
                                IsSort = true,
                                AllowInsert = true,
                                AllowUpdate = true,
                                IsFilter = (!column[3].Value.ToBool() && (sysDbType.ControlType == 1)),
                                IsFrozen = false,
                                IsUpdated = false,
                                GridWidth = 150,
                                LabelWidth = 100,
                                InputWidth = 200,
                                VaildKey = sysDbType.VaildKey,
                                RegValidate = "",
                                SysFieldTypeId = sysDbTypeid
                            };
                            switch (dbCol.ColumnName.ToLower())
                            {
                                case "createby":
                                    dbCol.ColumnCaption = "创建人";
                                    dbCol.AllowUpdate = false;
                                    dbCol.IsGridVisible = false;
                                    dbCol.IsEditVisible = false;
                                    dbCol.IsSort = false;
                                    break;
                                case "createdate":
                                    dbCol.ColumnCaption = "创建时间";
                                    dbCol.AllowUpdate = false;
                                    dbCol.IsGridVisible = false;
                                    dbCol.IsEditVisible = false;
                                    dbCol.IsSort = false;
                                    break;
                                case "lastmodifyuserid":
                                    dbCol.ColumnCaption = "修改人";
                                    dbCol.IsGridVisible = false;
                                    dbCol.IsEditVisible = false;
                                    dbCol.IsSort = false;
                                    break;
                                case "lastmodifydate":
                                    dbCol.IsGridVisible = true;
                                    dbCol.IsEditVisible = false;
                                    dbCol.IsSort = true;
                                    dbCol.IsDefaultSort = true;
                                    dbCol.ColumnCaption = "修改时间";
                                    break;
                                case "isdeleted":
                                    dbCol.IsGridVisible = false;
                                    dbCol.IsEditVisible = false;
                                    dbCol.ColumnCaption = "是否删除";
                                    break;
                                case "remark":
                                case "comments":
                                    dbCol.ColumnCaption = "描述";
                                    dbCol.IsGridVisible = false;
                                    break;
                            }
                            lstEntity.Add(dbCol);
                            this.SaveOrUpdate<DbColumnEntity>(dbCol);
                        }
                        //if (lstEntity != null && lstEntity.Count > 0)
                        //{
                        //    SessionFactory.BatchSave<DbColumnsEntity>(lstEntity);
                        //}
                        this.Commit();
                    }
                    catch (Exception ex)
                    {
                        this.Rollback();
                        throw ex;
                    }
                }

            }

            return lstEntity;
        }

        private int GetDbTypeId(string dbTypeName, int ProvderType)
        {
            Dictionary<string, DbFieldRelationTypeEntity> lstDbType = new Dictionary<string, DbFieldRelationTypeEntity>();

            if (!lstDbType.ContainsKey(dbTypeName.ToLower())
                || lstDbType[dbTypeName.ToLower()] == null)
            {

                ICriterion criter = Expression.And(Expression.Eq("IsDeleted", false),
                   Expression.And(Expression.Eq("DbProviderId", ProvderType),
                   Expression.Eq("DbTypeName", dbTypeName)));
                DbFieldRelationTypeEntity entity = this.Query<DbFieldRelationTypeEntity>(criter).FirstOrDefault();

                if (entity != null)
                {
                    lstDbType[dbTypeName.ToLower()] = entity;
                }
            }
            if (lstDbType.ContainsKey(dbTypeName.ToLower()) && lstDbType[dbTypeName.ToLower()] != null)
            {
                DbFieldRelationTypeEntity entity = lstDbType[dbTypeName.ToLower()];
                if (entity != null)
                {
                    return entity.DbFieldRelationTypeId;
                }
            }
            return 1;

        }

        /// <summary>
        /// VaildKey
        /// </summary>
        /// <param name="dbTypeName"></param>
        /// <param name="ProvderType"></param>
        /// <param name="VaildKey"></param>
        /// <returns></returns>
        private SysFieldTypeEntity GetVaildKey(string dbTypeName, int ProvderType)
        {
            ICriterion criter = Expression.Eq("IsDeleted", false);

            var lst = this.Query<SysFieldTypeEntity>(@"select b.* from " + DbFieldRelationTypeEntity._TableName + @" as a
                inner join " + SysFieldTypeEntity._TableName + " as b on a." + DbFieldRelationTypeEntity.__SysFieldTypeId + "=b." + SysFieldTypeEntity._PrimaryKeyName + @"
                where a.IsDeleted=0 and a.DbProviderId=" + ProvderType + " and a.DbTypeName='" + dbTypeName + "'", null);
            if (lst != null && lst.Count > 0)
            {
                return lst[0];
            }
            return null;

        }

        public bool CreateLanguage()
        {
            return true;
        }
        /// <summary>
        /// 获取数据源
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="data"></param>
        /// <returns></returns>
        public virtual LigerGrid GetDbSource(LigerGrid grid, int id)
        {
            var entity = this.GetById<DbTableEntity>(id.ToInt());
            if (entity == null)
            {
                return grid;
            }
            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId);

            List<DbColumnEntity> lstEntity = new List<DbColumnEntity>();
            var dbHelper = this.CreateRepository((ProviderType)dbServer.ProviderType, dbServer.ConntionString);
            return dbHelper.GetSource(entity.DbTableName, grid);


        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="grid"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public virtual byte[] Export(LigerGrid grid, int id)
        {
            var entity = this.GetById<DbTableEntity>(id.ToInt());
            if (entity == null)
            {
                return null;
            }
            DbServerEntity dbServer = this.GetById<DbServerEntity>(entity.DbServerId);
            var providerType = dbServer.ProviderType;
            List<DbColumnEntity> lstEntity = new List<DbColumnEntity>();
            var dbHelper = this.CreateRepository((ProviderType)dbServer.ProviderType, dbServer.ConntionString);
            dbHelper.GetSource(entity.DbTableName, grid);
            var dbColumn = this.GetColumns(entity.DbTableId);
            if (grid.Rows != null)
            {
                List<Dictionary<string, object>> dataRow = (List<Dictionary<string, object>>)grid.Rows;
                var visibleColumn = dbColumn.Where(x => x.IsEnabled && (x.IsEditVisible || x.IsGridVisible)).ToList();
                DataTable dt = new DataTable();
                List<string> title = new List<string>();
                List<string> columns = new List<string>();
                if (visibleColumn != null && visibleColumn.Count > 0)
                {
                    foreach (var item in visibleColumn)
                    {
                        title.Add(!string.IsNullOrWhiteSpace(item.ColumnCaption) ? item.ColumnCaption : item.ColumnName);
                        columns.Add(item.ColumnName);

                    }

                }
                return OfficeHelper.OutputExcel(dataRow, title, columns);

            }

            return null;

        }

        /// <summary>
        /// 验证导入表字段与表结构是否匹配
        /// </summary>
        /// <param name="dt">导入表</param>
        /// <param name="dbColumn">表结构字段</param>
        /// <param name="strError">错误信息</param>
        /// <returns></returns>
        public virtual bool CheckColumn(DataTable dt, List<DbColumnEntity> dbColumn, ref string strError)
        {

            var notnullCol = dbColumn.Where(x => !x.AllowDBNull && x.IsEnabled && !x.IsIdentifier && !SystemColumnNames.Contains(x.ColumnName.ToLower())).ToList();
            if (dt.Columns.Count < notnullCol.Count - 1)
            {
                strError = "该数据表与需要导入的表结构不匹配，无法导入";
                return false;
            }
            List<int> nullCol = new List<int>();
            int index = 0;
            var indexIdentifier = -1;
            #region 验证表字段是否正确
            foreach (DataColumn column in dt.Columns)
            {
                var obj = notnullCol.Where(x => x.ColumnName.ToLower().Trim().Equals(column.ColumnName.ToLower().Trim())
                 || (!string.IsNullOrWhiteSpace(x.ColumnCaption) && x.ColumnCaption.ToLower().Trim().Equals(column.ColumnName.ToLower().Trim()))).FirstOrDefault();
                if (obj != null)
                {
                    if (!obj.IsIdentifier)
                    {
                        column.ColumnName = obj.ColumnName;
                    }
                    else
                    {
                        indexIdentifier = index;
                    }

                }
                else
                {
                    nullCol.Add(index);
                }
                index++;
            }

            if (nullCol.Count > 0)
            {
                foreach (var i in nullCol)
                {
                    var obj = dbColumn.Where(x => x.AllowDBNull && (x.ColumnName.ToLower().Trim().Equals(dt.Columns[i].ColumnName.ToLower().Trim())
               || (!string.IsNullOrWhiteSpace(x.ColumnCaption) && x.ColumnCaption.ToLower().Trim().Equals(dt.Columns[i].ColumnName.ToLower().Trim())))).FirstOrDefault();
                    if (obj != null)
                    {
                        if (!obj.IsIdentifier)
                        {
                            dt.Columns[i].ColumnName = obj.ColumnName;
                        }

                    }
                    else
                    {

                    }
                }
            }
            if (indexIdentifier >= 0)
            {
                dt.Columns.RemoveAt(indexIdentifier);
            }
            #endregion

            return true;
        }

        public bool SaveColumnsCell(string id, DbColumnEntity dbColumn, ref string strError)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                strError = "参数值不正确";
                return false;
            }
            dbColumn.ColumnId = id;
            var relationTypeService = AppHttpContext.GetSerivce<IDbFieldRelationTypeService>();
            var typeModel = relationTypeService.GetById(dbColumn.DbFieldRelationTypeId);
            if (typeModel == null)
            {
                strError = "类型实体未找到";
                return false;
            }
            dbColumn.SysFieldTypeId = typeModel.SysFieldTypeId;
            UpdateColumnDescription(dbColumn,ref strError);
            return this.Update<DbColumnEntity>(dbColumn);

        }

        /// <summary>
        /// 数据同步
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="ligerGrid"></param>
        /// <param name="targgetTableName"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool syncTableData(int tableid, LigerGrid ligerGrid, string targgetTableName, ref string strError)
        {
            var sourceTable = this.GetById<DbTableEntity>(tableid);
            if (sourceTable == null)
            {
                strError = "表名不存在";
                return false;
            }
            var sourceServer = this.GetById<DbServerEntity>(sourceTable.DbServerId);
            if (sourceServer == null || sourceServer.TargetServerId <= 0)
            {
                strError = "数据库目标服务器不存在！";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(sourceServer.TargetServerId);
            if (targetServer == null || targetServer.DbServerId <= 0)
            {
                strError = "数据库目标服务器不存在！";
                return false;
            }
            LigerGrid targetligerGrid = JSONHelper.FromJson<LigerGrid>(JSONHelper.ToJson(ligerGrid));

            //var targetTable = this.Select<DbTableEntity>(
            //    Expression.And(Expression.Eq(DbTableEntity.__IsDeleted, false),
            //    Expression.And(Expression.Eq(DbTableEntity.__DbServerId,targetServer.DbServerId), 
            //    Expression.Eq(DbTableEntity.__DbTableName, sourceTable.DbTableName))));
            //if (targetTable == null)
            //{
            //    strError = "目标服务器中不存在该表，请先创建或者同步表！";
            //    return false;
            //}
            ICriterion criter = Expression.And(
                  Expression.Eq(DbTableEntity._PrimaryKeyName, tableid),

                 Expression.Eq(DbTableEntity.__IsDeleted, false));
            var lstColumn = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (lstColumn == null || lstColumn.Count <= 0)
            {
                strError = "请先同步字段！";
                return false;
            }

            var sourceHelper = this.CreateRepository((ProviderType)sourceServer.ProviderType, sourceServer.ConntionString);

            ligerGrid.pageIndex = 0;
            ligerGrid = sourceHelper.GetSource(sourceTable.DbTableName, ligerGrid);

            var sourceData = (List<Dictionary<string, object>>)ligerGrid.Rows;
            if (sourceData == null || sourceData.Count <= 0)
            {
                strError = "暂无需要同步的数据";
                return false;
            }
            var targetHelper = new AdoRepository((ProviderType)targetServer.ProviderType, targetServer.ConntionString);
            if (string.IsNullOrWhiteSpace(targgetTableName))
            {
                targgetTableName = sourceTable.DbTableName;
            }
            var targetTable = this.Select<DbTableEntity>(
                Expression.And(Expression.Eq(DbTableEntity.__IsDeleted, false),
                Expression.And(Expression.Eq(DbTableEntity.__DbServerId, targetServer.DbServerId),
                Expression.Eq(DbTableEntity.__DbTableName, targgetTableName))));

            if (targetTable == null || !targetTable.IsTable)
            {
                strError = "目标表不存在或者不是表，无法同步";
                return false;
            }
            ICriterion targetColumnsWhere = Expression.And(
                 Expression.Eq(DbColumnEntity.__DbTableId, targetTable.DbTableId),
                Expression.Eq(DbColumnEntity.__IsDeleted, false));
            var targetColumns = this.Query<DbColumnEntity>(targetColumnsWhere);
            if (targetColumns == null || targetColumns.Count <= 0)
            {
                strError = "目标表表结构错误，无法导入";
                return false;
            }
            var primaryKey = targetColumns.Where(x => x.IsPrimary).FirstOrDefault();
            if (primaryKey == null)
            {
                strError = "该表未设置主键，请先设置主键";
                return false;
            }
            if (sourceTable.IsTable)
            {
                targetligerGrid = targetHelper.GetSource(targgetTableName, targetligerGrid);
            }
            else
            {
                targetligerGrid = new LigerGrid();
                targetligerGrid.pageIndex = 0;
                targetligerGrid = targetHelper.GetSource(targgetTableName, targetligerGrid);
            }

            TaskHelper.TaskStart(targgetTableName + "表数据同步,同步记录" + sourceData.Count + "行", ApplicationEnvironments.DefaultSession.UserId, ApplicationEnvironments.DefaultSession.UserToken, (task) =>
             {
                 var targetData = (List<Dictionary<string, object>>)targetligerGrid.Rows;
                 if (targetData != null && targetData.Count > 0)
                 {
                     sourceData = sourceData.Where(x => targetData.Where(y => x[primaryKey.ColumnName].ToString().ToLower().Equals(y[primaryKey.ColumnName].ToString().ToLower())).FirstOrDefault() == null).ToList();
                 }
                 if (sourceData != null && sourceData.Count > 0)
                 {
                     List<TableSyncDomain> syncDomains = new List<TableSyncDomain>();
                     foreach (var item in sourceData)
                     {
                         if (task.TaskToken.Token.IsCancellationRequested)
                         {
                             return true;
                         }
                        //        targetColumnsWhere = Expression.And(Expression.And(
                        // Expression.Eq(DbColumnEntity.__DbTableId, targetTable.DbTableId),
                        //Expression.Eq(DbColumnEntity.__IsDeleted, false)), Expression.Eq(primaryKey.ColumnName, item.GetValueOrDefault(primaryKey.ColumnName)));
                        //        targetHelper.Select<Dictionary<string, object>>(targetHelper.get, targetColumnsWhere);
                        // this.Query<DbColumnEntity>(targetColumnsWhere);
                        TableSyncDomain syncDomain = new TableSyncDomain();
                         DataParameterCollection dbParameters = new DataParameterCollection(targetHelper);
                         Dictionary<string, object> itemValue = new Dictionary<string, object>();
                         foreach (var keyValue in item)
                         {
                             if (targetColumns.Where(y => keyValue.Key == y.ColumnName).FirstOrDefault() != null)
                             {
                                 itemValue.Add(keyValue.Key, keyValue.Value);
                             }
                         }
                         syncDomain.hql = targetHelper.GetInsert(targgetTableName, itemValue, ref dbParameters);
                         syncDomain.dbParameters = dbParameters;
                         syncDomains.Add(syncDomain);
                     }

                     using (var tran = targetHelper.Begin())
                     {
                         try
                         {
                             if (primaryKey.IsIdentifier)
                             {
                                 targetHelper.Identity_Disabled(targgetTableName);
                             }
                             foreach (var syncDomain in syncDomains)
                             {
                                 targetHelper.Execute(syncDomain.hql, syncDomain.dbParameters);
                                 if (task.TaskToken.Token.IsCancellationRequested)
                                 {
                                     targetHelper.Rollback();
                                     return true;
                                 }
                             }
                             if (primaryKey.IsIdentifier)
                             {
                                 targetHelper.Identity_Enable(targgetTableName);
                             }
                             targetHelper.Commit();
                         }
                         catch (Exception ex)
                         {
                             targetHelper.Rollback();
                             throw ex;
                         }

                     }
                 }

                 return true;
             });


            return true;
        }

        /// <summary>
        /// 同步表结构
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool syncTableStruct(int tableid, ref string strError)
        {
            var sourceTable = this.GetById<DbTableEntity>(tableid);
            if (sourceTable == null)
            {
                strError = "表名不存在";
                return false;
            }
            var sourceServer = this.GetById<DbServerEntity>(sourceTable.DbServerId);
            if (sourceServer == null || sourceServer.TargetServerId <= 0)
            {
                strError = "数据库源服务器不存在或已删除！";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(sourceServer.TargetServerId);
            if (targetServer == null || targetServer.DbServerId <= 0)
            {
                strError = "数据库目标服务器不存在！";
                return false;
            }
            ICriterion criter = Expression.And(
               Expression.Eq("DbTableId", tableid),
              Expression.Eq("IsDeleted", false));
            var sourceColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (sourceColumns == null || sourceColumns.Count <= 0)
            {
                strError = "请先同步字段！";
                return false;
            }
            var primaryKey = sourceColumns.Where(x => x.IsPrimary).FirstOrDefault();
            if (primaryKey == null)
            {
                strError = "该表未设置主键，请先设置主键";
                return false;
            }
            var targetTable = this.Select<DbTableEntity>(
                Expression.And(Expression.Eq(DbTableEntity.__IsDeleted, false),
                Expression.And(Expression.Eq(DbTableEntity.__DbServerId, targetServer.DbServerId),
                Expression.Eq(DbTableEntity.__DbTableName, sourceTable.DbTableName))));
            StringBuilder sqlEditer = new StringBuilder();
            if (targetTable == null)//不存在创建
            {

                List<DbColumnAttribute> dbColumns = (from x in sourceColumns select new DbColumnAttribute {
                    Name = x.ColumnName,
                    IsPrimaryKey = x.IsPrimary,
                    AllowDBNull = x.AllowDBNull,
                    Default = x.DefaultValue,
                    ColumnType = x.DbTypeName,
                    Identifier = x.IsIdentifier,
                    MaxLength = x.MaxLength,
                    Max = x.Max
                }).ToList();
                sqlEditer.Append(DbRepository.CreateTable(sourceTable.DbTableName, dbColumns));
                //strError = "目标服务器中不存在该表，请先创建或者同步表！";
                //return false;
            }
            else
            {
                criter = Expression.And(
               Expression.Eq("DbTableId", targetTable.DbTableId),
              Expression.Eq("IsDeleted", false));
                var targetColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
                if (targetColumns == null || targetColumns.Count <= 0)
                {
                    strError = "服务器表已存在，请先同步该表！";
                    return false;
                }
                var noColumns = sourceColumns.Where(x => (targetColumns.Where(y => x.ColumnName.Equals(y.ColumnName)).FirstOrDefault() == null)).ToList();
                if (noColumns != null && noColumns.Count > 0)
                {
                    foreach (var column in noColumns)
                    {
                        sqlEditer.Append(DbRepository.AddColumn(sourceTable.DbTableName, new DbColumnAttribute {
                            Name = column.ColumnName,
                            IsPrimaryKey = column.IsPrimary,
                            AllowDBNull = column.AllowDBNull,
                            Default = column.DefaultValue,
                            ColumnType = column.DbTypeName,
                            Identifier = column.IsIdentifier,
                            MaxLength = column.MaxLength,
                            Max = column.Max
                        }));
                    }
                }
            }
            if (string.IsNullOrWhiteSpace(sqlEditer.ToString()))
            {
                strError = "暂无需要更新的属性";
                return false;
            }
            var targetHelper = new AdoRepository((ProviderType)targetServer.ProviderType, targetServer.ConntionString);
            using (var tran = targetHelper.Begin())
            {
                try
                {
                    targetHelper.Execute(sqlEditer.ToString());
                    targetHelper.Commit();
                }
                catch (Exception ex)
                {

                    strError = ex.Message;
                    targetHelper.Rollback();
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 下载导出模板
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="tableName">名称</param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public byte[] DownTableTemplate(int tableid, ref string tableName, ref string strError)
        {
            var table = this.GetTableById(tableid);
            var lstAll = GetColumns(tableid);
            if (table != null && lstAll != null && lstAll.Count > 0)
            {
                tableName = table.LocalName;
                var lstTempte = new List<ExcelTemplateEntity>();
                lstAll = lstAll.Where(x => (x.IsEditVisible && x.IsEnabled) || (x.IsPrimary && (!x.IsIdentifier || string.IsNullOrWhiteSpace(x.DefaultValue)))).OrderBy(x => x.OrderNo).ToList();
                lstAll.ForEach(x =>
                {
                    lstTempte.Add(new ExcelTemplateEntity {
                        FieldName = x.ColumnName,
                        Name = x.ColumnCaption,
                        IsRequired = !x.AllowDBNull
                    });
                });
                if (lstTempte != null && lstTempte.Count > 0)
                {
                    return OfficeHelper.OutputExcelTemplate(lstTempte);
                }
            }
            strError = "未找到对应关系的结构";
            return null;

        }

        /// <summary>
        /// 数据导入
        /// </summary>
        /// <param name="tableid">目标表</param>
        /// <param name="file">导入文件</param>
        /// <param name="strError">错误信息</param>
        /// <returns></returns>
        public bool Import(int tableid, IFormFile file, ref string strError)
        {
            if (tableid <= 0)
            {
                strError = "参数不正确";
                return false;
            }
            var table = this.GetTableById(tableid);
            if (table == null)
            {
                strError = "表未找到";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(table.DbServerId);
            if (targetServer == null || targetServer.DbServerId <= 0)
            {
                strError = "数据库目标服务器不存在！";
                return false;
            }
            var lstAll = OfficeHelper.ImportExcel<Dictionary<string, string>>(file);
            strError = JSONHelper.ToJson(lstAll);
            if (lstAll != null && lstAll.Count > 0)
            {
                ICriterion criter = Expression.And(
                Expression.Eq(DbTableEntity._PrimaryKeyName, tableid),
                Expression.Eq(DbTableEntity.__IsDeleted, false));
                var targetHelper = new AdoRepository((ProviderType)targetServer.ProviderType, targetServer.ConntionString);
                var lstColumn = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
                if (lstColumn == null || lstColumn.Count <= 0)
                {
                    strError = "请先同步字段！";
                    return false;
                }
                var primaryKey = lstColumn.Where(x => x.IsPrimary).FirstOrDefault();
                if (primaryKey == null)
                {
                    strError = "该表未设置主键，请先设置主键";
                    return false;
                }
                List<TableSyncDomain> syncDomains = new List<TableSyncDomain>();
                int index = 0;

                var ifieldTypeService = AppHttpContext.GetSerivce<ISysFieldTypeService>();
                foreach (var rowItem in lstAll)
                {
                    bool isInsert = true;
                    Dictionary<string, object> resultParameter = new Dictionary<string, object>();
                    var primary = rowItem.Where(x => x.Key.ToLower().Equals(primaryKey.ColumnName.ToLower())
                        || (!string.IsNullOrWhiteSpace(primaryKey.ColumnCaption) && x.Key.ToLower().Equals(primaryKey.ColumnCaption.ToLower()))).FirstOrDefault();
                    string existSql = targetHelper.GetExistSql(table.DbTableName, primaryKey.ColumnName);

                    if (!primary.Equals(default(KeyValuePair<string, string>)) && !string.IsNullOrWhiteSpace(primary.Value))
                    {
                        DataParameterCollection primaryParameters = new DataParameterCollection(targetHelper);
                        primaryParameters.Add(primaryKey.ColumnName, primary.Value);
                        object obj = targetHelper.Scalar(existSql, primaryParameters);
                        if (obj != null && obj.ToString() == "1")
                        {
                            isInsert = false;
                        }
                    }
                    if (isInsert)
                    {
                        if (GetImportInsert(lstColumn, rowItem, index, ref resultParameter, ref strError))
                        {
                            TableSyncDomain syncDomain = new TableSyncDomain();
                            DataParameterCollection dbParameters = new DataParameterCollection(targetHelper);
                            syncDomain.hql = targetHelper.GetInsert(table.DbTableName, resultParameter, ref dbParameters);
                            syncDomain.dbParameters = dbParameters;
                            syncDomains.Add(syncDomain);
                        }
                        else
                        {
                            return false;
                        }
                    }
                    else
                    {
                        if (GetImportUpdate(lstColumn, rowItem, index, ref resultParameter, ref strError))
                        {
                            TableSyncDomain syncDomain = new TableSyncDomain();
                            DataParameterCollection dbParameters = new DataParameterCollection(targetHelper);
                            syncDomain.hql = targetHelper.GetUpdateSql(table.DbTableName, primaryKey.ColumnName, resultParameter, ref dbParameters);
                            syncDomain.dbParameters = dbParameters;
                            syncDomains.Add(syncDomain);
                        }
                        else
                        {
                            return false;
                        }
                    }
                    index++;
                }
                using (var tran = targetHelper.Begin())
                {
                    try
                    {
                        foreach (var syncDomain in syncDomains)
                        {
                            targetHelper.Execute(syncDomain.hql, syncDomain.dbParameters);
                        }
                        targetHelper.Commit();
                    }
                    catch (Exception ex)
                    {
                        targetHelper.Rollback();
                        strError = ex.Message;
                        return false;
                    }

                }
            }
            else
            {

                return false;
            }
            return true;
        }

        /// <summary>
        /// 获取新增的sql
        /// </summary>
        /// <param name="lstColumn"></param>
        /// <param name="rowItem"></param>
        /// <param name="resultRow"></param>
        /// <param name="index"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool GetImportInsert(List<DbColumnEntity> lstColumn, Dictionary<string, string> rowItem,
             int index, ref Dictionary<string, object> resultRow,
           ref string strError)
        {
            bool flag = false;
            foreach (var column in lstColumn)
            {
                if (!column.IsEnabled)
                {
                    continue;
                }
                if (column.IsPrimary && column.IsIdentifier)
                {
                    continue;
                }
                object itemValue = null;
                var row = rowItem.Where(x => x.Key.ToLower().Equals(column.ColumnName.ToLower())
                || (!string.IsNullOrWhiteSpace(column.ColumnCaption) && x.Key.ToLower().Equals(column.ColumnCaption.ToLower()))).FirstOrDefault();

                if (!row.Equals(default(KeyValuePair<string, string>)) && !string.IsNullOrWhiteSpace(row.Value))
                {
                    flag = VaildHelper.CheckInput("第" + index + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列", column.AllowDBNull,
                 column.VaildKey, true, row.Value.ToString(), 0, column.MaxLength, column.Min, column.Max, column.RegValidate, ref strError, column.DbTypeName);
                    if (!flag)
                    {
                        break;
                    }
                    itemValue = GetValue(column, column.DbTypeName.ToLower(), row.Value);
                    resultRow.Add(column.ColumnName, itemValue);
                }
                else if (SystemColumnNames.Contains(column.ColumnName.ToLower()))
                {
                    switch (column.ColumnName)
                    {
                        case EntityBase.__CreateBy:
                        case EntityBase.__LastModifyUserId:
                            resultRow.Add(column.ColumnName, ApplicationEnvironments.DefaultSession.UserId);
                            break;
                        case EntityBase.__CreateDate:
                        case EntityBase.__LastModifyDate:
                            resultRow.Add(column.ColumnName, DateTime.Now);
                            break;
                        case EntityBase.__IsDeleted:
                            resultRow.Add(column.ColumnName, false);
                            break;
                    }
                }
                else if (!column.AllowDBNull && string.IsNullOrWhiteSpace(row.Value))
                {
                    strError = "第" + (index) + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列不能为空";
                    return false;
                }
            }

            return flag;
        }


        public bool GetImportUpdate(List<DbColumnEntity> lstColumn, Dictionary<string, string> rowItem,
           int index, ref Dictionary<string, object> resultRow,
         ref string strError)
        {
            bool flag = false;
            foreach (var cellItem in rowItem)
            {
                var column = lstColumn.Where(x => x.ColumnName.ToLower().Equals(cellItem.Key.ToLower())
                 || (!string.IsNullOrWhiteSpace(x.ColumnCaption) && x.ColumnCaption.ToLower().Equals(cellItem.Key.ToLower()))).FirstOrDefault();
                if (column == null)
                {
                    strError = $"第{index}行 第({cellItem.Key})列未找到匹配字段";
                    return false;
                }
                if (!column.IsEnabled)
                {
                    strError = $"第{index}行 第({cellItem.Key})列未启用";
                    return false;
                }
                if (!column.AllowDBNull && string.IsNullOrWhiteSpace(cellItem.Value))
                {
                    strError = "第" + (index) + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列不能为空";
                    return false;
                }
                if (!string.IsNullOrWhiteSpace(cellItem.Value))
                {
                    flag = VaildHelper.CheckInput("第" + index + "行 第" + column.ColumnCaption + "(" + column.ColumnName + ")列", column.AllowDBNull,
                 column.VaildKey, true, cellItem.Value, 0, column.MaxLength, column.Min, column.Max, column.RegValidate, ref strError, column.DbTypeName);
                    if (!flag)
                    {
                        break;
                    }
                    var itemValue = GetValue(column, column.DbTypeName.ToLower(), cellItem.Value);
                    resultRow.Add(column.ColumnName, itemValue);
                }

            }

            return flag;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbTypeName">字段类型</param>
        /// <param name="value"></param>
        /// <returns></returns>
        public object GetValue(DbColumnEntity dbColumn, string dbTypeName, string value)
        {
            object itemValue = null;
            if (string.IsNullOrWhiteSpace(value))
            {
                return itemValue;
            }
            if (!string.IsNullOrWhiteSpace(dbColumn.Remark) && dbColumn.Remark.ToLower().Equals("b2cpassword"))
            {
                return MD5Helper.SHAEncrypt(value);
            }
            switch (dbTypeName)
            {
                case "long":
                    itemValue = Convert.ToInt64(value);
                    break;
                case "int":
                    itemValue = Convert.ToInt32(value);
                    break;
                case "float":
                case "double":
                case "decimal":
                    itemValue = Convert.ToDecimal(value);
                    break;
                case "dateTime":
                    itemValue = Convert.ToDateTime(value);
                    break;
                case "single":
                    itemValue = Convert.ToSingle(value);
                    break;
                case "bit":
                    itemValue = (value.ToLower() == "true" || value == "1");
                    break;
                default:
                    itemValue = value;
                    break;
            }
            return itemValue;

        }

        /// <summary>
        /// 表结构导出
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="ligerGrid"></param>
        /// <param name="targgetTableName"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public byte[] ExportTable(int tableid,ref string strError)
        {
            if (tableid <= 0)
            {
                strError = "参数不正确";
                return null;
            }
            var sourceTable = this.GetById<DbTableEntity>(tableid);
            if (sourceTable == null)
            {
                strError = "表名不存在";
                return null;
            }
            ICriterion criter = Expression.And(
                Expression.Eq("DbTableId", tableid),
               Expression.Eq("IsDeleted", false));
            var sourceColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (sourceColumns == null || sourceColumns.Count <= 0)
            {
                strError = "请先同步字段！";
                return null;
            }
            return OfficeHelper.OutputExcel(sourceColumns);
        }


        /// <summary>
        /// 表结构word导出
        /// </summary>
        /// <param name="tableid"></param>
        /// <param name="ligerGrid"></param>
        /// <param name="targgetTableName"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public byte[] ExportWordTable(int tableid, ref string strError)
        {
            if (tableid <= 0)
            {
                strError = "参数不正确";
                return null;
            }
            var sourceTable = this.GetById<DbTableEntity>(tableid);
            if (sourceTable == null)
            {
                strError = "表名不存在";
                return null;
            }
            ICriterion criter = Expression.And(
                Expression.Eq("DbTableId", tableid),
               Expression.Eq("IsDeleted", false));
            var sourceColumns = this.Query<DbColumnEntity>(criter, Order.Asc(DbColumnEntity.__OrderNo)).ToList();
            if (sourceColumns == null || sourceColumns.Count <= 0)
            {
                strError = "请先同步字段！";
                return null;
            }
            return OfficeHelper.OutputWord<DbColumnEntity>((!string.IsNullOrWhiteSpace(sourceTable.LocalName)? sourceTable.LocalName: sourceTable.DbTableName) +"数据字典",sourceColumns,null);
        }

        /// <summary>
        /// 更新字段描述
        /// </summary>
        /// <param name="dbColumn"></param>
        /// <param name="strError"></param>
        /// <returns></returns>
        public bool UpdateColumnDescription(DbColumnEntity dbColumn,ref string strError)
        {
            if (dbColumn.UpdateField== null || dbColumn.UpdateField.Count<= 0 || !dbColumn.UpdateField.Contains("ColumnCaption"))
            {
                return true;
            }
            var targetTable = this.GetById<DbTableEntity>(dbColumn.DbTableId);
            if (targetTable == null)
            {
                strError = "表名不存在";
                return false;
            }
            var targetServer = this.GetById<DbServerEntity>(targetTable.DbServerId);
            if (targetServer == null )
            {
                strError = "数据库源服务器不存在或已删除！";
                return false;
            }

            using (var targetHelper = new AdoRepository((ProviderType)targetServer.ProviderType, targetServer.ConntionString))
            {
                string existSql = targetHelper.ExistDescriptionSql(targetTable.DbTableName, dbColumn.ColumnName);
                var exist= targetHelper.Scalar(existSql);
                string updateSql = targetHelper.UpdateDescriptionSql(targetTable.DbTableName, dbColumn.ColumnName, dbColumn.ColumnCaption,(exist==null|| exist.ToString()!="1"));
                using (var tran = targetHelper.Begin())
                {
                    try
                    {
                        targetHelper.Execute(updateSql);
                        targetHelper.Commit();
                    }
                    catch (Exception ex)
                    {
                        strError = ex.Message;
                        targetHelper.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

     

    }
}
